import pandas as pd
import numpy as np
from datetime import datetime

# 读取原始数据
df = pd.read_csv('爬取的分类数据.csv')

# 确保中文显示正常
pd.set_option('display.unicode.east_asian_width', True)

# 创建结果数据框
result_columns = ['id', 'name', 'parent_id', 'level', 'sort', 'is_enabled', 'url', 'create_time', 'update_time']
result_df = pd.DataFrame(columns=result_columns)

# 获取当前时间（用于创建和更新时间）
current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

# 处理一级分类
first_categories = df['first_category'].unique()
first_category_ids = {}

# 为一级分类分配ID并添加到结果中
current_id = 1
for category in first_categories:
    first_category_ids[category] = current_id
    result_df.loc[len(result_df)] = {
        'id': current_id,
        'name': category,
        'parent_id': 0,
        'level': 1,
        'sort': 0,
        'is_enabled': 1,
        'url': np.nan,
        'create_time': current_time,
        'update_time': current_time
    }
    current_id += 1

# 处理二级分类
second_category_ids = {}

for first_category in first_categories:
    # 获取该一级分类下的所有二级分类
    second_categories = df[df['first_category'] == first_category]['second_category'].unique()
    
    for category in second_categories:
        # 创建二级分类唯一键
        key = (first_category, category)
        second_category_ids[key] = current_id
        
        result_df.loc[len(result_df)] = {
            'id': current_id,
            'name': category,
            'parent_id': first_category_ids[first_category],
            'level': 2,
            'sort': 0,
            'is_enabled': 1,
            'url': np.nan,
            'create_time': current_time,
            'update_time': current_time
        }
        current_id += 1

# 处理三级分类
for _, row in df.iterrows():
    first_category = row['first_category']
    second_category = row['second_category']
    third_category = row['third_category']
    url = row['url']
    
    # 检查二级分类ID是否已存在
    key = (first_category, second_category)
    if key not in second_category_ids:
        # 如果不存在，创建它
        second_category_ids[key] = current_id
        result_df.loc[len(result_df)] = {
            'id': current_id,
            'name': second_category,
            'parent_id': first_category_ids[first_category],
            'level': 2,
            'sort': 0,
            'is_enabled': 1,
            'url': np.nan,
            'create_time': current_time,
            'update_time': current_time
        }
        current_id += 1
    
    # 添加三级分类
    result_df.loc[len(result_df)] = {
        'id': current_id,
        'name': third_category,
        'parent_id': second_category_ids[key],
        'level': 3,
        'sort': 0,
        'is_enabled': 1,
        'url': url,
        'create_time': current_time,
        'update_time': current_time
    }
    current_id += 1

# 保存结果到CSV文件，使用utf-8-sig编码防止中文乱码
result_df.to_csv('分类数据sql.csv', index=False, encoding='utf-8-sig')

print(f'转换完成！共处理了 {len(result_df)} 条分类数据。')
print(f'结果已保存到：分类数据sql.csv')